# -*- coding: utf-8 -*-
import base64
import json
import os
import re
import time
import traceback

import openpyxl
import pymysql

t = {
"wait_task":"1",
"task_type":"4",
"messages":[
{
"company_id":"123"
}
]
}


def write_execle(messages):
    messages = eval(messages)
    print(messages['task_type'])
    # 模板文件路径
    file_path_model = 'C:/数据模板/'
    # 运行excle文件路径
    file_path_run = 'C:/社保任务/'

    # default = ['1城保参保单位人员增加', '2城保参保单位人员减少', '3职工工资申报', '4人员增减申请回盘表', '5添加人员增减申请回盘表任务',
    #            '6单位参保证明打印', '7添加单位参保证明打印任务', '8单位社保查询', '9添加单位社保查询任务', '10已申报查询']

    default = ['1', '2', '', '4', '5', '6', '7', '8', '9', '10', '']

    # 将公司id转为公司名称
    __, company = sql_select('select company_name from monitor where company_code'
                             '=\'{}\''.format(messages["messages"][0]['company_id']))
    # 异常处理
    try:
        messages["messages"][0]["company_id"] = str(company[0][0])
    except Exception as e:
        ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
        f = open(r'C:\log\log_write.txt', 'a+')
        f.write('====================================' * 4 + '\n')
        f.write('错误日志：' + '\t' + ts + '\n' + str(e) + '\n' + traceback.format_exc())
        f.write("错误语句：" + str(messages) + '\n')
        f.write('====================================' * 4 + '\n')
        f.close()

        # 记录数据库
        sql_text = 'insert into error_table(error,task_type,messages,error_time) values(\'{}\',\'{}\',\'{}\',\'{}\')'\
                   .format(re.sub('[\'\"]', '', str(e)), messages['task_type'],
                           re.sub('[\'\"]', '', str(messages['messages'][0])), ts)
        sql_update(sql_text)
        return {"status": "fail", "code": "-100", "code_message": "单位信息错误"}

    if messages['wait_task'] == '1':
        # 人员增减申请回盘表
        if default[3] == messages['task_type']:
            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            # 模板
            json_result_unitback = {"status": "success", "code": "0", "code_messages": "normal",
                                    "unitback_uptime": "", "unitback": []}

            # 写入excle文件
            # 获取时间戳
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '回盘打印模板.xlsx')
            sheet = open_file[open_file.sheetnames[0]]
            sheet['A2'].value = company[0][0]
            open_file.save('{}{}人员增减申请回盘表{}.xlsx'.format(file_path_run + '回盘打印/', company[0][0], time_stamp))

            for i in range(8640):

                # 设置查询频率
                time.sleep(20)
                sqltext = 'select date_format(unitback_uptime,\'%Y-%m-%d %H:%i:%S\') from monitor where company_name= '\
                          '\'{}\' and unitback_status =\'f\' and unitback_uptime > \'{}\''.format(company[0][0], t_time)
                sql_len, sql_time = sql_select(sqltext)

                sql_text = 'update monitor set unitback_status=\'d\' where company_name = \'{}\' and unitback_status ' \
                           '=\'f\' and unitback_uptime > \'{}\''.format(company[0][0], t_time)

                line = sql_update(sql_text)

                if line:
                    json_result_unitback['unitback_uptime'] = sql_time[0][0]
                    # 回盘 （先匹配公司名称查询记录）
                    sql_txt = 'select  personal_number,id_number,name,type_paper,' \
                              'sex,nation,registered_residence_type,date_birth,first_date,pension_status,' \
                              'pension_base,pension_time,unemployment_status,unemployment_base,unemployment_time,' \
                              'treat_status,treat_base,treat_time,hurt_status,hurt_base,hurt_time,brith_status,' \
                              'brith_base,birth_time,monthly_salary,identity,notes ' \
                              'from unitback where company_name=\'{}\''  \
                              ';'.format(messages['messages'][0]['company_id'])
                    sql_len, sql_tuplet = sql_select(sql_txt)

                    # 获取组装json
                    sql_txt = 'select column_name from information_schema.columns where ' \
                              'table_schema=\'social_security\' and table_name = \'unitback\';'
                    ijson = item_json(sql_txt, 'id', 'company_name')
                    for i in range(sql_len):
                        k = 0
                        mydict = {}
                        for j in ijson:
                            # print(sql_tuplet[i][k])
                            mydict[j] = sql_tuplet[i][k]

                            k = k + 1
                        json_result_unitback["unitback"].append(mydict)

                    # 计算任务数量
                    sql_count_text = 'update monitor set number = number-1 where company_name' \
                                     '=\'{}\';'.format(company[0][0])
                    sql_update(sql_count_text)

                    # 任务清零
                    sql_text = 'update monitor set unitback_status=\'f\' where company_name = ' \
                               '\'{}\';'.format(company[0][0])
                    sql_update(sql_text)

                    return json_result_unitback

                # 任务有效时间48小时
                if i == 8639:
                    ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
                    f = open(r'C:\log\log_write.txt', 'a+')
                    f.write('====================================' * 4 + '\n')
                    f.write('错误日志：' + '\t' + ts + '\n' + '任务超时' + '\n')
                    f.write("错误语句：" + str(messages) + '\n')
                    f.write('====================================' * 4 + '\n')
                    f.close()

                    # 记录数据库
                    sql_text = 'insert into error_table(error,task_type,error_time) values(\'{}\',\'{}\',\'{}\')' \
                        .format('任务超时', messages['task_type'], ts)
                    sql_update(sql_text)
                    return {"status": "fail", "code": "-600", "code_message": "任务超时"}
            # return {"status": "success", "code": "0", "code_message": "normal"}

        # 单位参保证明打印
        elif default[5] == messages['task_type']:
            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            file_path = 'C:/社保任务/单位参保证明打印/'
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # 模板
            json_result_prove = {"status": "success", "code": "0", "code_messages": "normal",
                                 "file_name": "", "file_data": ""}

            # 写入excle文件
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '单位参保证明打印模板.xlsx')
            sheet = open_file[open_file.sheetnames[0]]
            # 表格单元
            unit = ['A', 'B', 'C', 'D', 'E', 'F', 'G']

            # 数据筛选
            messages_s = ['company_id', 'pension', 'unemployment', 'hurt', 'company_type', 'start_time', 'end_time']
            # 表格行
            set_value = 2
            for i in messages['messages']:
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    # print(messages["messages"][set_value-2][(key_value[i_value])])
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][
                        messages_s[unit.index(j)]]

                    i_value = i_value + 1
                set_value = set_value + 1
            open_file.save('{}{}单位参保证明{}.xlsx'.format(file_path, company[0][0], time_stamp))

            # 将数据库文件信息改为过期
            sql_txt = 'update record_file set status=\'n\' where company_name=\'{}\' and ' \
                      'record_type=\'参保证明\''.format(company[0][0])
            sql_update(sql_txt)

            # 等待任务完成
            for i in range(8640):
                time.sleep(20)
                sql_txt = 'select file_path from record_file where company_name=\'{}\' and record_type=\'{}\' and ' \
                          'status=\'y\''.format(company[0][0], '参保证明')
                s_len, ms = sql_select(sql_txt)
                if s_len:
                    path_f = ms[0][0]
                    json_result_prove['file_name'] = path_f.split('/')[5]

                    # 文件数据化
                    f = open(path_f, 'rb')
                    b64 = f.read()

                    b64 = base64.b64encode(b64)
                    json_result_prove['file_data'] = b64.decode()

                    # 计算任务数量
                    sql_count_text = 'update monitor set number = number-1 where company_name' \
                                     '=\'{}\';'.format(company[0][0])
                    sql_update(sql_count_text)

                    return json_result_prove
                elif i >= 8639:
                    ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
                    f = open(r'C:\log\log_write.txt', 'a+')
                    f.write('====================================' * 4 + '\n')
                    f.write('错误日志：' + '\t' + ts + '\n' + '任务超时' + '\n')
                    f.write("错误语句：" + str(messages) + '\n')
                    f.write('====================================' * 4 + '\n')
                    f.close()

                    # 记录数据库
                    sql_text = 'insert into error_table(error,task_type,error_time) values(\'{}\',\'{}\',\'{}\')' \
                        .format('任务超时', messages['task_type'], ts)
                    sql_update(sql_text)
                    return {"status": "fail", "code": "-600", "code_message": "任务超时"}

        # 单位社保查询
        elif default[7] == messages['task_type']:

            file_path = 'C:/社保任务/单位社保查询/'
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # 写入excle文件
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '单位社保查询.xlsx')
            # 表格单元
            unit = ['A', 'B', 'C', 'D']

            # 数据筛选
            messages_s = ['company_id', 'company_ms', 'export_type', 'date']
            # 表格行
            set_value = 2
            for i in messages['messages']:
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    # print(messages["messages"][set_value-2][(key_value[i_value])])
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][
                        messages_s[unit.index(j)]]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}单位社保查询{}.xlsx'.format(file_path, company[0][0], time_stamp))

            # 将数据库文件信息改为过期
            sql_txt = 'update record_file set status=\'n\' where company_name=\'{}\' and ' \
                      'record_type=\'单位社保查询\''.format(company[0][0])
            sql_update(sql_txt)

            # 模板
            json_result_ssq = {"status": "success", "code": "0", "code_messages": "normal",
                               "files": []}
            files_s = {"file_name": "", "file_data": ""}

            # 等待任务完成
            for i in range(8640):
                time.sleep(20)
                sql_txt = 'select file_path from record_file where company_name=\'{}\' and record_type=\'{}\' and ' \
                          'status=\'y\''.format(company[0][0], '单位社保查询')
                s_len, ms = sql_select(sql_txt)
                if s_len:
                    path_f = ms[0][0]
                    s = path_f.split('/')[5]
                    path_s = path_f.strip(s)
                    ls = os.listdir(path_s)
                    for i in ls:
                        f_str = i.split('.')[-1]
                        if re.search('xl|png', f_str):
                            files_s['file_name'] = i.split('/')[-1]
                            f = open(path_s + i, 'rb')
                            b64 = f.read()
                            b64 = base64.b64encode(b64)
                            files_s['file_data'] = b64.decode()
                            json_result_ssq['files'].append(files_s)
                    return json_result_ssq
        # 已申报查询
        elif default[9] == messages['task_type']:
            file_path = 'C:/社保任务/已申报查询/'
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # 写入excle文件
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '已申报查询模板.xlsx')
            # 表格单元
            unit = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']
            messages_s = ['company_id', 'query_type', 'id_number', 'name', 'start_time', 'end_time',
                          'certainly_time', 'status', 'dow']
            messages['messages'][0]['dow'] = '是'

            # 表格行
            set_value = 2
            for i in messages['messages']:
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    # print(messages["messages"][set_value-2][(key_value[i_value])])
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][
                        messages_s[unit.index(j)]]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}已申报查询{}.xlsx'.format(file_path, company[0][0], time_stamp))

            for i in range(43200):
                print(i)
                time.sleep(2)
                len_s, _ = sql_select('select record_type from record_file where company_name=\'x\'')
                if len_s:
                    if i > 43198:
                        return {"status": "fail", "code": "-600", "code_message": "任务超时"}
                    continue
                else:
                    # 将数据库文件信息改为过期
                    sql_txt = 'update record_file set status=\'n\' where ' \
                              'record_type=\'已申报查询\' and company_name=\'{}\''.format(company[0][0])
                    sql_update(sql_txt)
                    break

            # 模板
            json_result_q = {"status": "success", "code": "0", "code_messages": "normal",
                             "files": [{"file_name": "", "file_data": ""}]}
            # json_result_q = {"status": "success", "code": "0", "code_messages": "normal",
            #                  "results": []}
            # results = {"results":"","company_name": "", "query_type": "","id_number":"","name":"",
            #            "start_time":"","end_time":"","certainly_time":"","status": ""}

            # 等待任务完成
            for i in range(8640):
                time.sleep(2)
                sql_txt = 'select file_path from record_file where record_type=\'{}\' and ' \
                          'status=\'y\' and company_name=\'{}\''.format('已申请查询', company[0][0])
                s_len, ms = sql_select(sql_txt)
                if s_len:
                    t = ['0', '1', '3', '12', '13', '14']
                    work = openpyxl.load_workbook(ms[0][0])
                    sheet = work.active
                    for i in range(sheet.max_row-1):
                        k = 0
                        default = {}
                        for j in range(15):
                            if not str(j) in t:
                                list_s = list(results.keys())
                                up = sheet.cell(i + 2, j + 1).value
                                if not up:
                                    up = ' '
                                default[list_s[k]] = up
                                k += 1
                        print(default)
                        json_result_q['results'].append(default)

                    return json_result_q

        else:
            ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
            f = open(r'C:\log\log_write.txt', 'a+')
            f.write('====================================' * 4 + '\n')
            f.write('错误日志：' + '\t' + ts + '\n' + 'task_type参数错误' + '\n')
            f.write("错误语句：" + str(messages) + '\n')
            f.write('====================================' * 4 + '\n')
            f.close()

            # 记录数据库
            sql_text = 'insert into error_table(error,task_type,error_time) values(\'{}\',\'{}\',\'{}\')' \
                .format('task_type参数错误', messages['task_type'], ts)
            sql_update(sql_text)
            return {"status": "fail", "code": "-300", "code_message": "task_type参数错误"}

    elif messages['wait_task'] == '0':

        # 城保城保参保单位人员增加
        if default[0] == messages['task_type']:
            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            # 获取时间戳
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # excel对应单元
            unit = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
                    'R', 'S']
            open_file = openpyxl.load_workbook(file_path_model + '城保参保单位人员增加模板.xlsx')
            # 定义写入文件行初始值
            set_value = 2
            for i in messages["messages"]:
                key_value = list(i.keys())
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][(key_value[i_value])]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}城保参保单位人员增加模板{}.xlsx'.format
                           (file_path_run + '城保参保单位人员增加/', company[0][0], time_stamp))

            # 计算任务数量
            sql_count_text = 'update monitor set number = number-1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            return {"status": "success", "code": "0", "code_message": "normal"}

        # 城保参保单位人员减少模板
        if default[1] == messages['task_type']:
            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            # 获取时间戳
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # excel对应单元
            unit = ['A', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
            open_file = openpyxl.load_workbook(file_path_model + '城保参保单位人员减少模板.xlsx')
            # 定义写入文件行初始值
            set_value = 2
            for i in messages["messages"]:
                key_value = list(i.keys())
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][(key_value[i_value])]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}城保参保单位人员减少模板{}.xlsx'.format
                           (file_path_run + '城保参保单位人员减少/', company[0][0], time_stamp))

            # 计算任务数量
            sql_count_text = 'update monitor set number = number-1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            return {"status": "success", "code": "0", "code_message": "normal"}

        # 人员增减申请回盘表
        if default[3] == messages['task_type']:

            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            # 获取时间
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

            # 模板
            json_result_unitback = {"status": "success", "code": "0", "code_messages": "normal",
                                    "unitback_uptime": "", "unitback": []}

            for i in range(8640):
                # 设置查询频率
                time.sleep(20)
                sqltext = 'select date_format(unitback_uptime,\'%Y-%m-%d %H:%i:%S\') from monitor where company_name= '\
                          '\'{}\' and unitback_status =\'f\''.format(company[0][0], t_time)
                sql_len, sql_time = sql_select(sqltext)

                sql_text = 'update monitor set unitback_status=\'d\' where company_name = \'{}\' and unitback_status ' \
                           '=\'f\''.format(company[0][0])

                line = sql_update(sql_text)
                if line:
                    json_result_unitback['unitback_uptime'] = sql_time[0][0]
                    # 回盘 （先匹配公司名称查询记录）
                    sql_txt = 'select  personal_number,id_number,name,type_paper,' \
                              'sex,nation,registered_residence_type,date_birth,first_date,pension_status,' \
                              'pension_base,pension_time,unemployment_status,unemployment_base,unemployment_time,' \
                              'treat_status,treat_base,treat_time,hurt_status,hurt_base,hurt_time,brith_status,' \
                              'brith_base,birth_time,monthly_salary,identity,notes ' \
                              'from unitback where company_name=\'{}\''  \
                              ';'.format(messages['messages'][0]['company_id'])
                    sql_len, sql_tuplet = sql_select(sql_txt)

                    # 获取组装json
                    sql_txt = 'select column_name from information_schema.columns where ' \
                              'table_schema=\'social_security\' and table_name = \'unitback\';'
                    ijson = item_json(sql_txt, 'id', 'company_name')
                    for i in range(sql_len):
                        k = 0
                        mydict = {}
                        for j in ijson:
                            # print(sql_tuplet[i][k])
                            mydict[j] = sql_tuplet[i][k]

                            k = k + 1
                        json_result_unitback["unitback"].append(mydict)

                    # 计算任务数量
                    sql_count_text = 'update monitor set number = number-1 where company_name ' \
                                     '=\'{}\';'.format(company[0][0])
                    sql_update(sql_count_text)

                    # 任务清零
                    sql_text = 'update monitor set unitback_status=\'f\' where company_name = ' \
                               '\'{}\';'.format(company[0][0])
                    sql_update(sql_text)

                    return json_result_unitback

                # 任务有效时间48小时
                if i == 8639:
                    ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
                    f = open(r'C:\log\log_write.txt', 'a+')
                    f.write('====================================' * 4 + '\n')
                    f.write('错误日志：' + '\t' + ts + '\n' + '任务超时' + '\n')
                    f.write("错误语句：" + str(messages) + '\n')
                    f.write('====================================' * 4 + '\n')
                    f.close()

                    # 记录数据库
                    sql_text = 'insert into error_table(error,task_type,error_time) values(\'{}\',\'{}\',\'{}\')' \
                        .format('任务超时', messages['task_type'], ts)
                    sql_update(sql_text)

                    # 任务清零
                    sql_text = 'update monitor set unitback_status=\'f\' where company_name = ' \
                               '\'{}\';'.format(company[0][0])
                    sql_update(sql_text)

                    return {"status": "fail", "code": "-600", "code_message": "任务超时"}

        # 添加人员增减申请回盘表任务
        elif default[4] == messages['task_type']:

            # 计算任务数量
            sql_count_text = 'update monitor set number = number+1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            # 写入excle文件
            # 获取时间戳
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '回盘打印模板.xlsx')
            sheet = open_file[open_file.sheetnames[0]]
            sheet['A2'].value = company[0][0]
            open_file.save('{}{}人员增减申请回盘表{}.xlsx'.format(file_path_run + '回盘打印/', company[0][0], time_stamp))

            # 计算任务数量
            sql_count_text = 'update monitor set number = number-1 where company_name=\'{}\';'.format(company[0][0])
            sql_update(sql_count_text)

            return {"status": "success", "code": "0", "code_message": "normal"}

        # 单位参保证明打印
        elif default[5] == messages['task_type']:

            # 模板
            json_result_prove = {"status": "success", "code": "0", "code_messages": "normal",
                                 "file_name": "", "file_data": ""}

            sql_txt = 'select file_path from record_file where company_name=\'{}\' and record_type=\'{}\' and ' \
                      'status=\'{}\''.format(company[0][0], '参保证明', 'y')
            s_len, ms = sql_select(sql_txt)

            if s_len:
                path_f = ms[0][0]
                json_result_prove['file_name'] = path_f.split('/')[5]

                # 文件数据化
                f = open(path_f, 'rb')
                b64 = f.read()

                b64 = base64.b64encode(b64)
                json_result_prove['file_data'] = b64.decode()

                return json_result_prove
            else:
                return json_result_prove

        # 添加单位参保证明打印任务
        elif default[6] == messages['task_type']:

            file_path = 'C:/社保任务/单位参保证明打印/'
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # 写入excle文件
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '单位参保证明打印模板.xlsx')
            sheet = open_file[open_file.sheetnames[0]]
            # 表格单元
            unit = ['A', 'B', 'C', 'D', 'E', 'F', 'G']

            # 数据筛选
            messages_s = ['company_id', 'pension', 'unemployment', 'hurt', 'company_type', 'start_time', 'end_time']
            # 表格行
            set_value = 2
            for i in messages['messages']:
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    # print(messages["messages"][set_value-2][(key_value[i_value])])
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][
                        messages_s[unit.index(j)]]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}单位参保证明{}.xlsx'.format(file_path, company[0][0], time_stamp))
            return {"status": "success", "code": "0", "code_message": "normal"}
        # 单位社保查询
        elif default[7] == messages['task_type']:

            # 模板
            json_result_ssq = {"status": "success", "code": "0", "code_messages": "normal",
                               "files": []}
            files_s = {"file_name": "", "file_data": ""}

            sql_txt = 'select file_path from record_file where company_name=\'{}\' and record_type=\'{}\' and ' \
                      'status=\'{}\''.format(company[0][0], '单位社保查询', 'y')
            s_len, ms = sql_select(sql_txt)

            if s_len:

                path_f = ms[0][0]
                s = path_f.split('/')[5]
                path_s = path_f.strip(s)
                ls = os.listdir(path_s)
                for i in ls:
                    f_str = i.split('.')[-1]
                    if re.search('xl', f_str):
                        files_s['file_name'] = i.split('/')[-1]
                        f = open(path_s + i, 'rb')
                        b64 = f.read()
                        b64 = base64.b64encode(b64)
                        files_s['file_data'] = b64.decode()
                        json_result_ssq['files'].append(i)

                return json_result_ssq
            else:
                return json_result_ssq

        # 添加社保查询任务
        elif default[8] == messages['task_type']:
            file_path = 'C:/社保任务/单位社保查询/'
            t_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            time_stamp = int(time.mktime(time.strptime(t_time, "%Y-%m-%d %H:%M:%S")))

            # 写入excle文件
            # excel写入单位名称
            open_file = openpyxl.load_workbook(file_path_model + '单位社保查询.xlsx')
            # 表格单元
            unit = ['A', 'B', 'C', 'D']

            # 数据筛选
            messages_s = ['company_id', 'company_ms', 'export_type', 'date']
            # 表格行
            set_value = 2
            for i in messages['messages']:
                i_value = 0
                for j in unit:
                    sheet = open_file[open_file.sheetnames[0]]
                    # print(messages["messages"][set_value-2][(key_value[i_value])])
                    sheet['{}{}'.format(j, set_value)].value = messages["messages"][set_value - 2][
                        messages_s[unit.index(j)]]

                    i_value += 1
                set_value += 1
            open_file.save('{}{}单位社保查询{}.xlsx'.format(file_path, company[0][0], time_stamp))
            return {"status": "success", "code": "0", "code_message": "normal"}
        # 无效参数
        else:
            ts = time.strftime("%Y-%m-%d %H:%M", time.localtime())
            f = open(r'C:\log\log_write.txt', 'a+')
            f.write('====================================' * 4 + '\n')
            f.write('错误日志：' + '\t' + ts + '\n' + 'task_type参数错误' + '\n')
            f.write("错误语句：" + str(messages) + '\n')
            f.write('====================================' * 4 + '\n')
            f.close()

            # 记录数据库
            sql_text = 'insert into error_table(error,task_type,error_time) values(\'{}\',\'{}\',\'{}\')' \
                .format('task_type参数错误', messages['task_type'], ts)
            sql_update(sql_text)
            return {"status": "fail", "code": "-300", "code_message": "task_type参数错误"}


# 查询数据库信息
def sql_select(sql_text):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="social_security", password="753951", charset="utf8")
    cursor = conn.cursor()
    sql_str = cursor.execute(sql_text)
    sql_tuplet = []
    for i in range(sql_str):
        sql_tuplet.append(list(cursor.fetchone()))
    cursor.close()
    conn.close()
    return sql_str, sql_tuplet


# 修改数据库信息
def sql_update(up_sql):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="social_security", password="753951", charset="utf8")
    cursor = conn.cursor()
    line = cursor.execute(up_sql)
    conn.commit()
    cursor.close()
    conn.close()
    return line


# 组装数据库表列名
def item_json(sql_text, id_sql, company):
    conn = pymysql.connect(host="127.0.0.1", user="root", database="social_security", password="753951", charset="utf8")
    cursor = conn.cursor()
    sql_str = cursor.execute(sql_text)
    sql_tuplet = []
    for i in range(sql_str):
        sql_tuplet.append(list(cursor.fetchone()))
    # 组装json
    sql_json = {}
    for i in sql_tuplet:
        sql_json[i[0]] = ""
    # 删除字典键值数据
    del sql_json[id_sql], sql_json[company]
    cursor.close()
    conn.close()
    return sql_json


# write_execle(t)
